package database.app;

import database.common.ConnectionFactory;
import model.app.Department;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   3/10/13
 * Time:   10:33 PM
 */
public class DepartmentDao {

    private static final String INSERT_DEPARTMENT =
            "INSERT INTO app.department ( emertimi, adresa, funksioni, enabled, parent_id) VALUES (?,?,?,?,?)";
    private static final String UPDATE_DEPARTMENT =
            "UPDATE app.department SET emertimi=?, adresa=?, funksioni=?, enabled=?, parent_id=? WHERE id=? ";
    private static final String DELETE_DEPARTMENT =
            "DELETE FROM app.department WHERE id = ?";
    private static final String SELECT_SINGLE_DEPARTMENT =
            "SELECT id, emertimi, adresa, funksioni, enabled, parent_id FROM app.department WHERE id = ?";
    private static final String SELECT_ALL_DEPARTMENTS =
            "SELECT * from query_to_xml('SELECT * FROM app.department',true,false,'')";
    private static final String SELECT_CHILD_DEPARTMENTS =
            "SELECT id, emertimi, adresa, funksioni, enabled, parent_id FROM app.department WHERE parent_id=?";


    public static int insert(Department department){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(INSERT_DEPARTMENT);
            statement.setString(1, department.getEmertimi());
            statement.setString(2, department.getAdresa());
            statement.setString(3, department.getFunksioni());
            statement.setInt(4, department.getEnabled());
            statement.setInt(5, department.getParentID());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int update(Department department){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(UPDATE_DEPARTMENT);
            statement.setString(1, department.getEmertimi());
            statement.setString(2, department.getAdresa());
            statement.setString(3, department.getFunksioni());
            statement.setInt(4, department.getEnabled());
            statement.setInt(5, department.getParentID());
            statement.setInt(6, department.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static int delete(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(DELETE_DEPARTMENT);
            statement.setInt(1, id);
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Department selectDepartment(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Department department = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_DEPARTMENT);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                department = new Department();
                department.setId(resultSet.getInt(1));
                department.setEmertimi(resultSet.getString(2));
                department.setAdresa(resultSet.getString(3));
                department.setFunksioni(resultSet.getString(4));
                department.setEnabled(resultSet.getInt(5));
                department.setParentID(resultSet.getInt(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return department;
    }

    public static String selectAllDepartments(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String result = "";
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_ALL_DEPARTMENTS);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public static Department getOrganigram(int root){
        Department rootDepartment=selectDepartment(root);
        List<Department> children=selectChildDepartments(rootDepartment.getId());
        for (Department child:children){
            child=getOrganigram(child.getId());
            rootDepartment.addChildDepartment(child);
        }
        return rootDepartment;
    }

    public static List<Department> selectChildDepartments(int parent){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Department> children =null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_CHILD_DEPARTMENTS);
            statement.setInt(1, parent);
            resultSet = statement.executeQuery();
            children=new ArrayList<Department>();
            while (resultSet.next()){
                Department department = new Department();
                department.setId(resultSet.getInt(1));
                department.setEmertimi(resultSet.getString(2));
                department.setAdresa(resultSet.getString(3));
                department.setFunksioni(resultSet.getString(4));
                department.setEnabled(resultSet.getInt(5));
                department.setParentID(resultSet.getInt(6));
                children.add(department);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return children;
    }

}
